#!perl  -I../scripts/lib
use strict;
use Spreadsheet::ParseExcel;
use Getopt::Long;

my %h_options=();

sub cmdline_error
{
	my ($message) = @_;
	warn "$message\n" if (defined $message);
#	print "Error command\n";
	exit;
}

unless (Getopt::Long::GetOptions(\%h_options,
	"check",
	"getdata"))
{
cmdline_error("usage: -check|-getdata");
}

if (not exists $h_options{'check'} and not exists $h_options{'getdata'}) {
    cmdline_error("at least one of <check> or <getdata> should be provided");
}

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;
my $self={};
my $oBook = $oExcel->Parse($ARGV[0]);
#print "$oBook\n";
if ($oBook)
{
}
else
{
exit(2);
}
my($iR, $iC, $oWkS, $oWkC);
#print "FILE:", $oBook->{File} , "\n";
#print "COUNT :", $oBook->{SheetCount} , "\n";

#print "AUTHOR:", $oBook->{Author} , "\n"
#if defined $oBook->{Author};
my $return;
my $sheet_flag=0;
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
	{
	$oWkS = $oBook->{Worksheet}[$iSheet];
#	print "--------- SHEET:", $oWkS->{Name}, "\n";
	if ($oWkS->{Name}=~/Demandes/i){
	$sheet_flag=1;
	for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
		{
		for(my $iC = $oWkS->{MinCol} ;defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;$iC++)
			{
			$oWkC = $oWkS->{Cells}[$iR][$iC];
			if ($oWkC)
			{
			my $value=$oWkC->Value;
			if ($value=~/Expediteur/i)
				{
				$self->{'Expediteur'}{'IC'}=$iC;
				}
                	elsif ($value=~/Copie/i)
                        	{
	                        $self->{'Copie'}{'IC'}=$iC;
        	                }
                        elsif ($value=~/Titre/i)
                	        {
                        	$self->{'Titre'}{'IC'}=$iC;
                        	}
                        elsif ($value=~/Date/i)
                        	{
	                        $self->{'Date'}{'IC'}=$iC;
        	                }
                        elsif ($value=~/Heure/i)
                	        {
                        	$self->{'Heure'}{'IC'}=$iC;
	                        }
                        elsif ($value=~/IMEI/i)
        	                {
                	        $self->{'IMEI'}{'IC'}=$iC;
				$self->{'IR'}=$iR;
				goto VA;
                       		}
			}
			}
		}
VA:
if ($sheet_flag!=1)
{
 exit(3);
}
if (! defined $self->{'Expediteur'}{'IC'})
{
 exit(4);
}
if (! defined $self->{'Copie'}{'IC'})
{
exit(4);
}
if (! defined $self->{'Titre'}{'IC'})
{
exit(4);
}
if (! defined $self->{'Date'}{'IC'})
{
exit(4);
}
if (! defined $self->{'Heure'}{'IC'})
{
exit(4);
}
if (! defined $self->{'IMEI'}{'IC'})
{
exit(4);
}
if (exists $h_options{'check'})
{

exit(1);
}
	 for(my $iR = $self->{'IR'}+1 ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
                {
		my $key;
		my $oWkC;
		my $value;
		$oWkC = $oWkS->{Cells}[$iR][$self->{'Expediteur'}{'IC'}];
		if ($oWkC)
		{
		$key=$oWkC->Value;
		$key=~ s/\n//g;
		$key=~ s/^\s//;
		$key=~ s/\s$//;
		}
		else
		{
		$key="";
		}
		$oWkC = $oWkS->{Cells}[$iR][$self->{'Copie'}{'IC'}];
		if ($oWkC)
		{
		$value=$oWkC->Value;
		$value=~ s/\n//g;
		$key=~ s/^\s//;
                $key=~ s/\s$//;
		$key=$key."\;".$value;
		}
		else
                {
                $key=$key."\;"."";
                }
		$oWkC = $oWkS->{Cells}[$iR][$self->{'Titre'}{'IC'}];
		if ($oWkC)
		{
		$value=$oWkC->Value;
		$value=~ s/\n//g;
		$key=~ s/^\s//;
                $key=~ s/\s$//;
                $key=$key."\;".$value;
		}
		else
                {
                $key=$key."\;"."";
                }
		$oWkC = $oWkS->{Cells}[$iR][$self->{'Date'}{'IC'}];
		if ($oWkC)
		{
		$value=$oWkC->Value;
		$value=~ s/\n//g;
		$key=~ s/^\s//;
                $key=~ s/\s$//;
		if ($value)
		{
                my @da=split (/\-/,$value);
		@da[1]="01",if (@da[1]=~ /Jan/i);
		@da[1]="02",if (@da[1]=~ /Feb/i);
		@da[1]="03",if (@da[1]=~ /Mar/i);
		@da[1]="04",if (@da[1]=~ /Apr/i);
		@da[1]="05",if (@da[1]=~ /May/i);
		@da[1]="06",if (@da[1]=~ /Jun/i);
		@da[1]="07",if (@da[1]=~ /Jul/i);
		@da[1]="08",if (@da[1]=~ /Aug/i);
		@da[1]="09",if (@da[1]=~ /Sep/i);
		@da[1]="10",if (@da[1]=~ /Oct/i);
		@da[1]="11",if (@da[1]=~ /Nov/i);
		@da[1]="12",if (@da[1]=~ /Dec/i);
		@da[0]="0".@da[0],if (@da[0]=~ /^\d$/);
		$value=join ("-",@da[1],@da[0]);
		$key=$key."\;".$value;
		}
		else
		{
		$key=$key."\;"."";
		}
		}
		else
                {
                $key=$key."\;"."";
                }
		$oWkC = $oWkS->{Cells}[$iR][$self->{'Heure'}{'IC'}];
		if ($oWkC)
		{
		$value=$oWkC->Value;
		$value=~ s/\n//g;
		$key=~ s/^\s//;
                $key=~ s/\s$//;
                if ($value)
		{
		my @ti=split (/\:/,$value);
		@ti[0]="0".@ti[0],if (@ti[0]=~ /^\d$/);
		@ti[1]="0".@ti[1],if (@ti[1]=~ /^\d$/);
		$value=join (":",@ti[0],@ti[1]);
		$key=$key." ".$value;
		}
		}
		else
                {
                #$key=$key."";
                }
		$oWkC = $oWkS->{Cells}[$iR][$self->{'IMEI'}{'IC'}];
		if ($oWkC)
		{
		$value=$oWkC->Value;
		$value=~ s/\n//g;
		$key=~ s/^\s//;
                $key=~ s/\s$//;
                $key=$key."\;".$value;
		}
		else
                {
                $key=$key."\;"."";
                }
		if ($key=~ /;;;;/)
		{
		}
		else
		{
		$return=$return.$key."\n";
		}
		}
}
	}
$return=~ s/\n$//;
print "$return";
